
package vn.aptech.project.model.dao;
import java.io.FileNotFoundException;
import java.sql.SQLException;
import vn.aptech.project.model.dto.*;
import vn.aptech.project.view.variable;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

public class  product_DAO extends CommonDAO {
 public  product_DAO() throws ClassNotFoundException, SQLException, IOException{
 }


public Vector selectall() throws SQLException
{
String strSelect = "SELECT productid,FS_category.typeinfo,productname,unitprice,quantity FROM FS_product inner join FS_category on FS_category.Typeid=FS_product.Typeid";
return ExecSelect(strSelect);
}
public Vector totalproduct() throws SQLException
{
String strSelect = "select tongsanpham=count('productid') from FS_product";
return ExecSelect(strSelect);
}
public Vector top5product() throws SQLException
{
String strSelect = "select Top 5 FS_product.productname,sum(FS_orderline.quantity) from FS_orderline inner join FS_product on FS_product.productid=FS_orderline.productid group by FS_orderline.productid,productname order by sum(FS_orderline.quantity) DESC";
return ExecSelect(strSelect);
}
public int getProductidByProductName(String productname) throws SQLException, FileNotFoundException, IOException
{
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
             } catch (ClassNotFoundException ex)
            {
            Logger.getLogger(tblorder_DAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            String connectionString = "jdbc:sqlserver://localhost:1433;databaseName=FurnitureSystem;user=sa;password=1234567";
            Connection con = DriverManager.getConnection(connectionString);
            Statement statement = con.createStatement();
            String str = "Select productid from FS_product where productname="+productname;
            ResultSet rs = statement.executeQuery(str);
            rs.next();
            Vector vt = new Vector();
            vt.add(rs.getObject("productid"));
            int a =Integer.parseInt(String.valueOf(vt.get(0)));
            return a;

}
public Vector SearchByName(String productname) throws SQLException
{
String strSelect = "SELECT productid,typeinfo,productname,unitprice,quantity FROM product inner join FS_category on FS_category.Typeid=FS_product.Typeid where productname like '%"+productname+"%'";
return ExecSelect(strSelect);
}
public Vector selectByCategory(String Typeinfo) throws SQLException
{            
    String strSelect = "SELECT productid,typeinfo,productname,unitprice,quantity FROM FS_product inner join FS_category on FS_category.Typeid=FS_product.Typeid where FS_category.typeinfo='"+Typeinfo+"'";
    return ExecSelect(strSelect);
}
public Vector selectProductByTypeid(int typeid) throws SQLException
{
String strSelect = "SELECT * FROM FS_product where Typeid="+typeid;
return ExecSelect(strSelect);
}
public Vector SearchById(int productid) throws SQLException
{
String strSelect = "SELECT productid,typeinfo,productname,unitprice,quantity FROM FS_product inner join FS_category on FS_category.Typeid=FS_product.Typeid where productid like "+productid;
return ExecSelect(strSelect);
}
public Vector SearchByIdAndByName(int productid,String productname) throws SQLException
{
String strSelect = "SELECT productid,typeinfo,productname,unitprice,quantity FROM FS_product inner join FS_category on FS_category.Typeid=FS_product.Typeid where productid like "+productid+" and productname like '%"+productname+"%'";
return ExecSelect(strSelect);
}
public int insert(product_DTO  obj) throws SQLException
{
String strInsert = "INSERT INTO FS_product VALUES (N'"+  obj.getproductname() +"'  ,"+ obj.getunitprice() +" ,"+ obj.getquantity() +" ,"+ obj.getTypeid() +")";
return  ExecNonQuery(strInsert);
}


public int update(product_DTO  obj) throws SQLException
{
String strUpdate = "UPDATE FS_product SET productname= N'"+ obj.getproductname()+"' ,"+"unitprice= "+obj.getunitprice() +","+"quantity= "+obj.getquantity() +","+"Typeid= "+obj.getTypeid() +"WHERE productid="+ obj.getproductid() ;
return  ExecNonQuery(strUpdate);
}
public int updatequantity(int quantity,int productid) throws SQLException
{
String strUpdate = "UPDATE FS_product SET quantity="+quantity+" where productid="+productid;
return  ExecNonQuery(strUpdate);
}

public int delete(int productid) throws SQLException
{
String strDelete = "DELETE FROM FS_product WHERE productid="+productid;
return  ExecNonQuery(strDelete);
}
public Vector selectall1() throws SQLException
{
String strSelect = "SELECT productid,FS_category.typeinfo,productname,unitprice,quantity FROM FS_product inner join FS_category on FS_category.Typeid=FS_product.Typeid inner join FS_Employee_Detail on FS_category.typeinfo=FS_Employee_Detail.department where FS_Employee_Detail.staffname='"+variable.StaffName+"'";
return ExecSelect(strSelect);
}
public Vector SearchByIdAndByName1(int productid,String productname) throws SQLException
{
String strSelect = "SELECT productid,typeinfo,productname,unitprice,quantity FROM FS_product inner join FS_category on FS_category.Typeid=FS_product.Typeid inner join FS_Employee_Detail on FS_category.typeinfo=FS_Employee_Detail.department where productid like "+productid+" and productname like '%"+productname+"%' and FS_Employee_Detail.staffname='"+variable.StaffName+"'";
return ExecSelect(strSelect);
}
public Vector SearchById1(int productid) throws SQLException
{
String strSelect = "SELECT productid,typeinfo,productname,unitprice,quantity FROM FS_product inner join FS_category on FS_category.Typeid=FS_product.Typeid inner join FS_Employee_Detail on FS_category.typeinfo=FS_Employee_Detail.department where productid like "+productid+" and FS_Employee_Detail.staffname='"+variable.StaffName+"'";
return ExecSelect(strSelect);
}
public Vector SearchByName1(String productname) throws SQLException
{
String strSelect = "SELECT productid,typeinfo,productname,unitprice,quantity FROM FS_product inner join FS_category on FS_category.Typeid=FS_product.Typeid inner join FS_Employee_Detail on FS_category.typeinfo=FS_Employee_Detail.department where productname like '%"+productname+"%' and FS_Employee_Detail.staffname='"+variable.StaffName+"'";
return ExecSelect(strSelect);
}
}

